package searchurl.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashSet;
import java.util.Set;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import searchurl.common.SearchUrlContants;
import searchurl.common.TextFileTools;

/**
 *
 * @author iridiumcao
 */
public class MsExcelTools {

    /**
     *
     * convert an excel file to several text files, as every sheet maps one text file
     *
     * @param excelPath
     * @return
     */
    public static boolean convertToTextFiles(String excelPath) {
        boolean flag = false;
        try {
            File excel = new File(excelPath);
            Workbook wb = null;
            if (excelPath.endsWith(".xls")) {
                InputStream stream = new FileInputStream(new File(excelPath));
                wb = new HSSFWorkbook(stream);
            } else if (excelPath.endsWith(".xlsx")) {
                wb = new XSSFWorkbook(excelPath);
            }

            int numberOfSheet = wb.getNumberOfSheets();
            for (int i = 0; i < numberOfSheet; i++) {
                Sheet sheet = wb.getSheetAt(i);
                String sheetName = sheet.getSheetName();
                File sheetFile = new File(excel.getParentFile(), sheetName + ".txt");
                int rowNum = sheet.getLastRowNum();
                StringBuilder sb = new StringBuilder();
                for (int j = 0; j <= rowNum; j++) {
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        Cell cell = row.getCell(0);
                        if (cell != null) {
                            sb.append(cell).append(SearchUrlContants.LINE_SEPARATOR);
                        }
                    }
                }
                TextFileTools.writeTextToFile(sb.toString(), sheetFile);
                flag = true;
            }
        } catch (FileNotFoundException e) {
            flag = false;
            e.printStackTrace();
        } catch (IOException e) {
            flag = false;
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * Known issue: if the value of a cell is a number, even if it's an integer, 
     * this function gets a number with a number point dot and a zero. But it's 
     * not need to get value exactly as it shows because a sensitive word is hardly
     * an integer number. (Nov 22, 2011)
     * 
     * @param excelPath
     * @return
     */
    public static Set<String> fetchWordsFromExcel(String excelPath) {
        Set<String> words = new HashSet<String>();
        boolean flag = false;
        try {
            Workbook wb = null;
            if (excelPath.endsWith(".xls")) {
                InputStream stream = new FileInputStream(new File(excelPath));
                wb = new HSSFWorkbook(stream);
            } else if (excelPath.endsWith(".xlsx")) {
                wb = new XSSFWorkbook(excelPath);
            }

            int numberOfSheet = wb.getNumberOfSheets();
            for (int i = 0; i < numberOfSheet; i++) {
                Sheet sheet = wb.getSheetAt(i);
                int rowNum = sheet.getLastRowNum();
                for (int j = 0; j <= rowNum; j++) {
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        Cell cell = row.getCell(0);
                        words.add(cell.toString());
                    }
                }
                flag = true;
            }
        } catch (FileNotFoundException e) {
            flag = false;
            e.printStackTrace();
        } catch (IOException e) {
            flag = false;
            e.printStackTrace();
        }
        return words;
    }
}